using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using MyClever.Lib;

namespace MyClever.DefaultPlugins
{
    public class WriteSQLite : MissionPlugin
    {
        [Package(Direction.Input, "Defines the setting for writing or creating the SQLite database file.")]
        enum SQLightDBFile
        {
            [Column(typeof(string), "The SQLite database file, you like edit/create.")]
            FilePath,
            [Column(typeof(string), "The table name, you like to edit/create.")]
            TableName,
            [Column(typeof(bool), "If true, the new table supports an integer primary key ID.")]
            AddPrimaryKey,
            [Column(typeof(string), "Is given, the new table supports a column which saves the date of every row creation.")]
            CreateAtColumnName
        }

        [Package(Direction.Input, "Defines the data, which should written in the database.")]
        enum DBData { }

        [Plugin(PluginDevStatus.Production, "Write data in your favorite table of your expected SQLite database file. If the database/table is not existing, the plugin creates the expected database/table.")]
        public WriteSQLite()
        {
        }

        public override void Initialize()
        {
            
            //This is the first Function called by the host...
            //Put anything needed to start with here first
        }

        public override void Dispose()
        {
            //Put any cleanup code in here for when the program is stopped
        }

        /// <summary>
        /// Generate new Source-content.
        /// </summary>
        protected override void Work()
        {            
            DataTableReader sqliteDBPackage = this.GetDataReader(typeof(SQLightDBFile));
            while (sqliteDBPackage.Read())
            {
                this.AddNewLoggingMessage(typeof(SQLightDBFile), String.Format("write table: '{0}' in file: {1}", sqliteDBPackage[SQLightDBFile.TableName.ToString()].ToString(), sqliteDBPackage[SQLightDBFile.FilePath.ToString()].ToString()));
                         
                SQLiteConnection connection = new SQLiteConnection();
                connection.ConnectionString = "Data Source=" + sqliteDBPackage[SQLightDBFile.FilePath.ToString()].ToString();
                connection.Open();

                DataTableReader dbDataPackage = this.GetDataReader(typeof(DBData));
                StringBuilder commandTextBuilder = new StringBuilder();
                commandTextBuilder.Append("CREATE TABLE IF NOT EXISTS " + sqliteDBPackage[SQLightDBFile.TableName.ToString()].ToString());
                commandTextBuilder.Append("( ");

                if (true == Convert.ToBoolean(sqliteDBPackage[SQLightDBFile.AddPrimaryKey.ToString()]))
                {
                   commandTextBuilder.Append("ID INTEGER PRIMARY KEY, ");
                }

                if (false == String.IsNullOrEmpty(sqliteDBPackage[SQLightDBFile.CreateAtColumnName.ToString()].ToString()))
                {
                    commandTextBuilder.Append(sqliteDBPackage[SQLightDBFile.CreateAtColumnName.ToString()].ToString() + " TIMESTAMP DEFAULT (DATETIME('now')), ");
                }

                commandTextBuilder.Append(this.GetSQLColumnDefinitions(this.GetColumnNames(dbDataPackage)));
                commandTextBuilder.Append(");");

                SQLiteCommand command = new SQLiteCommand(connection);
                command.CommandText = commandTextBuilder.ToString();
                command.ExecuteNonQuery();
                commandTextBuilder.Remove(0, commandTextBuilder.Length);

                while (dbDataPackage.Read())
                {                    
                    //this.AddNewLoggingMessage(typeof(SQLightDBFile), String.Format("write table: '{0}' in file: {1}", sqliteDBPackage[SQLightDBFile.TableName.ToString()].ToString(), sqliteDBPackage[SQLightDBFile.FilePath.ToString()].ToString()));
                    commandTextBuilder.Append("INSERT INTO " + sqliteDBPackage[SQLightDBFile.TableName.ToString()].ToString() + " (");
                    foreach (KeyValuePair<string, Type> columnOn in this.GetColumnNames(dbDataPackage))
                    {
                        commandTextBuilder.Append(columnOn.Key.Replace(' ', '_') + ",");
                    }
                    commandTextBuilder.Remove(commandTextBuilder.Length - 1, 1);
                    commandTextBuilder.Append(") VALUES(");

                    foreach (KeyValuePair<string, Type> columnOn in this.GetColumnNames(dbDataPackage))
                    {
                        commandTextBuilder.Append("'");
                        //SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).
                        if (typeof(bool) == columnOn.Value)
                        {
                            commandTextBuilder.Append(Convert.ToInt32(Convert.ToBoolean(dbDataPackage[columnOn.Key].ToString())));
                        }
                        else
                        {
                            commandTextBuilder.Append(dbDataPackage[columnOn.Key].ToString().Replace("'", "\""));
                        }
                        commandTextBuilder.Append("',");
                    }
                    commandTextBuilder.Remove(commandTextBuilder.Length - 1, 1);                   
                    commandTextBuilder.Append("); ");
                }
                command.CommandText = commandTextBuilder.ToString();
                command.ExecuteNonQuery();

                // Freigabe der Ressourcen.
                command.Dispose();

                connection.Close();
                connection.Dispose();
            }            
        }

        private string GetSQLColumnDefinitions(List<KeyValuePair<string, Type>> columns)
        {            
            string returnValue = "";
            foreach (KeyValuePair<string, Type> columnOn in columns)
	        {
                returnValue += columnOn.Key.Replace(' ', '_') +" " + GetSQLColumnType(columnOn.Value) + ","; 
	        }
            return returnValue.Substring(0, returnValue.Length - 1);
        }

        private string GetSQLColumnType(Type columnType)
        {
            if (typeof(Int16) == columnType || typeof(Int32) == columnType || typeof(Int64) == columnType
                || typeof(UInt16) == columnType || typeof(UInt32) == columnType || typeof(UInt64) == columnType)
	        {
        		 return "INTEGER";
	        }
            else if (typeof(double) == columnType || typeof(float) == columnType)
            {
                return "REAL";
            }
            else if (typeof(DateTime) == columnType || typeof(Decimal) == columnType)
            {
                return "NUMERIC";
            }
            else if (typeof(Boolean) == columnType)
            {
                return "BOOLEAN";
            }
            else if (typeof(string) == columnType)
            {
                return "TEXT";
            }
            else
            {
                return "NONE";
            }
        }
    }
}
